<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <style>
        h1 {
            font-size: 48px;
            width: auto;
            height: auto;
            text-align: center;
            font-family: "Microsoft YaHei UI", serif;
        }

        .FileChooser {
            width: 100%;
            height: auto;
            margin: 0 auto;
        }

        .FileChooser p {
            font-size: 24px;
        }

        .FileChooser .dataFormat {
            color: orange;
        }

        .FileChooser .Waring {
            color: red;
        }

        .FileChooser .btn_group {
            margin-top: 2px;
            margin-bottom: 4px;
        }

        .FileChooser .Excel {
            width: 50%;
            height: auto;
            margin-top: 2px;
        }

        .showFileData {
            width: 80%;
            height: 70%;
            margin: 10px auto;
            box-sizing: border-box;
            overflow: auto;
        }

        table {
            margin: 0 auto;
            width: 90%;
            height: auto;
        }

        table tr {
            width: 100%;
            height: auto;
            border: 2px solid #c0c4cc;
        }

        table tr td {
            width: 14%;
            height: auto;
            border: 2px solid #59c2c5;
        }

        .status {
            width: 16%;
            height: auto;
            border: 2px solid #59c2c5;
        }

        .topTab {
            table-layout: fixed;
        }

        #flush_btn {
            display: none;
        }

        h2 {
            text-align: center;
            color: #01aaed;
            display: none;
        }
    </style>
</head>
<body>
<script crossorigin="anonymous"
        integrity="sha512-r22gChDnGvBylk90+2e/ycr3RVrDi8DIOkIGNhJlKfuyQM4tIRAI062MaV8sfjQKYVGjOBaZBOA87z+IhZE9DA=="
        src="https://lib.baomitu.com/xlsx/0.18.5/xlsx.full.min.js"></script>
<h1>Ecxel文件导入</h1>
<div class="FileChooser">
    <p>请上传包含学生<span class="dataFormat">班级，姓名，性别，学号，综合分数，系统登陆密码</span>的文件</p>
    <p class="Waring" id="dan">
        请严格按照上述模板进行列排序,并不要在Excel文件里添加标题,写入数据库时页面会无响应20秒左右，请勿刷新或者进行其他操作！</p>
    <input accept=".xls,.xlsx" class="Excel layui-input-inline" id="File" type="file"/>
    <div class="btn_group">
        <button class="layui-btn layui-btn-normal" onclick="getData()">解析文件</button>
        <button class="layui-btn layui-btn-normal" id="flush_btn">写入数据库</button>
    </div>
</div>
<h2 id="dataTitle">学生信息</h2>
<div class="showFileData">
    <table class="layui-table" id="datas">
    </table>
</div>
</body>
<script>
    function showData() {
        let file = $("#File").prop("files");
        let reader = new FileReader();
        try {
            reader.readAsBinaryString(file[0]);
            layer.open({
                title: "从表格导入",
                anim: 2,
                content: "数据解析成功",
                area: ['200px', '180px'],
                offset: 'auto',
                fixed: true,
                btn: ['关闭']
            })
        } catch (e) {
            console.log(e.toString());
            alert("未选择文件");
        }


        let processedData = [];
        reader.onload = (e) => {
            let data = e.target.result;
            let workbook = XLSX.read(data, {type: "binary"});
            let sheetNames = workbook.SheetNames; // 工作表名称集合
            sheetNames.forEach((name) => {
                let worksheet = workbook.Sheets[name]; // 只能通过工作表名称来获取指定工作表
                let rowData = [];//用来接收excel数据
                let index = 0;
                for (let key in worksheet) {
                    // v是读取单元格的原始值
                    rowData[index] = worksheet[key].v;//将表格的遍历存储
                    index++;
                }
                //去出头尾的无效数据
                for (let a = 1; a < rowData.length - 1; a++) {
                    processedData[a - 1] = rowData[a];
                }
                let tableTarget = document.getElementById("datas");
                let htmlTarget = '<thead class="topTab"> <tr ><th>班级</th><th>姓名</th><th>性别</th><th>学号</th><th>分数</th><th>密码</th><th>状态</th></tr></thead>';
                for (index = 0; index < processedData.length; index++) {
                    htmlTarget += '<tr>';
                    for (let temp = index; temp <= index + 5; temp++) {
                        htmlTarget = htmlTarget + '<td>' + processedData[temp] + '</td>';
                    }
                    index += 5;
                    htmlTarget += '<td class="status">等待导入</td></tr>';
                }
                htmlTarget += '</tbody>';
                $("#flush_btn").css("display", "inline");
                $("#dataTitle").css("display", "block");
                tableTarget.innerHTML = htmlTarget;

            });
        }
        return processedData;
    }

    function getData() {
        let results = new Map();
        let flush_btn = document.getElementById("flush_btn");
        let id = 1;
        let data = showData();//显示数据并拿到初始数组
        flush_btn.onclick = (function () {
            for (let a = 0; a < data.length; a += 6) {/*对数据进行切片，分包发送*/
                let classes = data[a];
                let name = data[a + 1];
                let gender = data[a + 2];
                let stuid = data[a + 3];
                let score = data[a + 4];
                let password = data[a + 5];

                $.ajax({
                        headers: {
                            "Content-Type": "application/json"
                        },
                        url: '/studentData/import',
                        async: false,
                        data: JSON.stringify({classes, name, gender, stuid, score, password, id}),//对数据进行JSON序列化
                        dataType: 'json',
                        method: 'POST',
                        success: function (result) {
                            if (result.status === 'true') {
                                console.log(result.message);
                                results.set(stuid, result.message);
                            } else {
                                console.log(result.message);
                                results.set(stuid, result.message);
                            }
                        },
                    }
                );
                id++;
            }
            layer.open({
                title: "从表格导入",
                anim: 2,
                content: "操作完成,请查看导入结果",
                area: ['200px', '180px'],
                offset: 'auto',
                fixed: true,
                btn: ['关闭']
            })
            let tableTarget = document.getElementById("datas");
            let index, temp;
            let htmlTarget = '<tbody> <tr><td>班级</td><td>姓名</td><td>性别</td><td>学号</td><td>分数</td><td>密码</td><td>状态</td></tr>';
            for (index = 0; index < data.length; index++) {
                htmlTarget += '<tr>';
                for (temp = index; temp <= index + 5; temp++) {
                    htmlTarget = htmlTarget + '<td>' + data[temp] + '</td>';
                }
                htmlTarget += '<td class="status">' + results.get(data[index + 3]) + '</td></tr>';
                index += 5;

            }
            htmlTarget += '</tbody>';
            tableTarget.innerHTML = htmlTarget;

        })

    }


</script>
</html>